--（1）编写一个函数求该银行的金额总和
create function getMoney()
returns money
as
begin
	declare @money money
	set @money = (select sum(CardMoney) from BankCard)
	return @money
end
select dbo.getMoney() 总金额

--（2）传入账户编号，返回账户真实姓名

create function getName(@cardNo varchar(20))
returns varchar(5)
as
begin
	declare @name varchar(5)
	set @name = (select a.RealName from AccountInfo a inner join BankCard b on a.AccountId = b.AccountId where b.CardNo=@cardNo)
	return @name
end
select dbo.getName('6225547854125656') 姓名

--（3）传递开始时间和结束时间，返回交易记录（存钱取钱），交易记录中包含 真实姓名，卡号，存钱金额，取钱金额，交易时间。
create function getCharge(@beginTime smalldatetime ,@endTime smalldatetime)
returns @Charge table(name varchar(10),cardNo varchar(20),saveMoney money,outMoney money ,getTime smalldatetime)
as
begin
	insert into @Charge
	select a.RealName,b.CardNo,c.MoneyInBank,c.MoneyOutBank,c.ExchangeTime from AccountInfo a inner join BankCard b on a.AccountId = b.AccountId
	inner join CardExchange c on c.CardNo = b.CardNo where c.ExchangeTime between cast(@beginTime  as smalldatetime) and cast(@endTime as smalldatetime)
	return
end
select * from getCharge('2022-09-19 ','2022-10-29')

--（4）查询银行卡信息，将银行卡状态1,2,3,4分别转换为汉字“正常,挂失,冻结,注销”，根据银行卡余额显示银行卡等级 30万以下为“普通用户”,
--30万及以上为"VIP用户"，分别显示卡号，身份证，姓名，余额，用户等级，银行卡状态。
create function func_getMessage(@state int,@money money)
returns @message table(
	CardNo varchar(50),
	CardState int,
	state varchar(50),
	CardMoney money,
	lv varchar(50)

)
as
begin
	declare @sta varchar(50)
	insert into @message
	select 
		CardNo,
		CardState,
		case
			when CardState = 1 then '正常'
			when CardState = 2 then '挂失'
			when CardState = 3 then '冻结'
			else '注销'
		end ,
		CardMoney ,
		case 
			when CardMoney < 300000 then '普通用户' 
			else 'VIP用户'
		end 
	from BankCard
	return
end
go
select * from func_getMessage(1,30)

--（5）编写函数，根据出生日期求年龄，年龄求实岁，例如：
create function getAge(@Time smalldatetime)
returns int 
as
begin
	declare @year int = year(getdate())
	declare @month int = month(getdate())
	declare @day int = day(getdate())
	declare @age int  = @year - year(@Time)
	if(@month <= month(@Time))
		begin
			if(@month = month(@Time))
				begin
					if(@day < day(@Time))
						set @age = @year - year(@Time)-1
				end
				set @age = @year - year(@Time)-1
		end
	else
	set @age = @year - year(@Time)
	return @age
end
drop function getAge
select dbo.getAge('2003-11-08')

